/**
 * 
 */
package org.huanglei.account.provider;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.huanglei.account.bean.Account;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;

/**
 * @author huanglei
 * @data Jul 13, 2012
 * @tag 保存数据到手机本地
 */
public class LocalDataProvider extends SQLiteOpenHelper implements DataProvider {

	/**
	 * 数据库字段
	 */
	private interface AccountColumn extends BaseColumns {
		/**
		 * 类型
		 */
		public static final String TYPE = "type";
		/**
		 * 金额
		 */
		public static final String MONEY = "money";
		/**
		 * 日期
		 */
		public static final String DATE = "date";
		/**
		 * 备注
		 */
		public static final String MEMO = "memo";

		/**
		 * 列索引位置
		 */
		public static final int INDEX_ID = 0;
		public static final int INDEX_TYPE = 1;
		public static final int INDEX_MONEY = 2;
		public static final int INDEX_DATE = 3;
		public static final int INDEX_MEMO = 4;

	}

	/**
	 * sql语句常量
	 */
	public static final String DATABASE_NAME = "account.db";

	private static int DATABASE_VERSION = 1;

	private static final String TABLE_NAME = "account";

	public static final String[] COLUMNS = { AccountColumn._ID,
			AccountColumn.TYPE, AccountColumn.MONEY, AccountColumn.DATE,
			AccountColumn.MEMO };

	private static final String SQL_CREATE = "CREATE TABLE " + TABLE_NAME
			+ " (" + AccountColumn._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
			+ AccountColumn.TYPE + " TEXT," + AccountColumn.MONEY + " REAL,"
			+ AccountColumn.DATE + " LONG," + AccountColumn.MEMO + " TEXT)";

	/**
	 * @param context
	 * @param name
	 * @param factory
	 * @param version
	 */
	public LocalDataProvider(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * org.huanglei.account.provider.DataProvider#save(org.huanglei.account.
	 * bean.Account)
	 */
	@Override
	public long save(Account account) {
		SQLiteDatabase db = getWritableDatabase();
		try {
			ContentValues values = new ContentValues();
			values.put(AccountColumn.TYPE, account.getType());
			values.put(AccountColumn.MONEY, account.getMoney());
			values.put(AccountColumn.DATE, account.getDate());
			values.put(AccountColumn.MEMO, account.getMemo());
			return db.insert(TABLE_NAME, null, values);
		} finally {
			if (db.isOpen()) {
				db.close();
			}
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * org.huanglei.account.provider.DataProvider#update(org.huanglei.account
	 * .bean.Account)
	 */
	@Override
	public long update(Account account) {
		// TODO Auto-generated method stub
		return 0;
	}
	

	@Override
	public long delete(int id) {
		
		SQLiteDatabase db = getReadableDatabase();
		int a = 0;
		try {
			
			a = db.delete(TABLE_NAME,AccountColumn._ID + "=?", new String[] { "" + id });
		
		} finally {
			if (db.isOpen()) {
				db.close();
			}
		}
		
		return a;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.huanglei.account.provider.DataProvider#getAccount(int)
	 */
	@Override
	public Account getAccount(int id) {
		Account account = null;
		SQLiteDatabase db = getReadableDatabase();
		try {
			Cursor cursor = db.query(TABLE_NAME, COLUMNS, AccountColumn._ID
					+ "=?", new String[] { "" + id }, null, null,
					AccountColumn.DATE, "");

			while (cursor.moveToNext()) {
				account = Account
						.newAccount(cursor.getInt(AccountColumn.INDEX_ID))
						.setType(cursor.getString(AccountColumn.INDEX_TYPE))
						.setMoney(cursor.getDouble(AccountColumn.INDEX_MONEY))
						.setDate(cursor.getLong(AccountColumn.INDEX_DATE))
						.setMemo(cursor.getString(AccountColumn.INDEX_MEMO));
			}
		} finally {
			if (db.isOpen()) {
				db.close();
			}
		}
		return account;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.huanglei.account.provider.DataProvider#getAccounts(int, int)
	 */
	@Override
	public List<Account> getAccounts() {
		SQLiteDatabase db = getReadableDatabase();
		List<Account> accounts = new ArrayList<Account>();
		try {
			Cursor cursor = db.query(TABLE_NAME, COLUMNS, null, null, null,
					null, AccountColumn._ID + " asc", null);
			while (cursor.moveToNext()) {
				accounts.add(Account
						.newAccount(cursor.getInt(AccountColumn.INDEX_ID))
						.setType(cursor.getString(AccountColumn.INDEX_TYPE))
						.setMoney(cursor.getDouble(AccountColumn.INDEX_MONEY))
						.setDate(cursor.getLong(AccountColumn.INDEX_DATE))
						.setMemo(cursor.getString(AccountColumn.INDEX_MEMO)));
			}
		} finally {
			if (db.isOpen()) {
				db.close();
			}
		}
		return accounts;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see org.huanglei.account.provider.DataProvider#getAccounts(int, int)
	 */
	@Override
	public List<Account> getAccounts(int page, int count) {
		SQLiteDatabase db = getReadableDatabase();
		List<Account> accounts = new ArrayList<Account>();
		try {
			Cursor cursor = db
					.query(TABLE_NAME, COLUMNS, null, null, null, null,
							AccountColumn.DATE + " desc", "" + page + ","
									+ count);
			while (cursor.moveToNext()) {
				accounts.add(Account
						.newAccount(cursor.getInt(AccountColumn.INDEX_ID))
						.setType(cursor.getString(AccountColumn.INDEX_TYPE))
						.setMoney(cursor.getDouble(AccountColumn.INDEX_MONEY))
						.setDate(cursor.getLong(AccountColumn.INDEX_DATE))
						.setMemo(cursor.getString(AccountColumn.INDEX_MEMO)));
			}
		} finally {
			if (db.isOpen()) {
				db.close();
			}
		}
		return accounts;
	}


	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * org.huanglei.account.provider.DataProvider#getMonthStatic(java.lang.String
	 * )
	 */
	@Override
	public Map<String, Double> getDateStatic(String date,
			StaticDateFormat staticDateFormat) {
		SQLiteDatabase db = getReadableDatabase();
		Map<String, Double> map = staticDateFormat.getKey();
		try {
			Cursor cursor = db.query(TABLE_NAME, new String[] {
					"strftime('" + staticDateFormat + "',datetime("
							+ AccountColumn.DATE + "/1000, 'unixepoch'))",
					"sum(" + AccountColumn.MONEY + ")" }, null, null,
					"strftime('" + staticDateFormat + "',datetime("
							+ AccountColumn.DATE + "/1000, 'unixepoch'))",
					null, null);
			while (cursor.moveToNext()) {
				String dt = cursor.getString(0);
				String sdt = ""
						+ Integer
								.valueOf(dt.substring(dt.lastIndexOf("-") + 1));
				if (map.containsKey(sdt)) {
					map.put(sdt, cursor.getDouble(1));
				}
			}
		} finally {
			if (db.isOpen()) {
				db.close();
			}
		}
		return map;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * android.database.sqlite.SQLiteOpenHelper#onCreate(android.database.sqlite
	 * .SQLiteDatabase)
	 */
	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(SQL_CREATE);
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * android.database.sqlite.SQLiteOpenHelper#onUpgrade(android.database.sqlite
	 * .SQLiteDatabase, int, int)
	 */
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

	}

	@Override
	public String getTotal() {

		String total = "";
		int w = 1000;

		total = "总收入:" + w + " 支出:" + w + " 小计:" + w;

		return total;
	}

	@Override
	public Map<String, Double> getStatistics(String date,
			StaticDateFormat staticDateFormat) {
		Map<String, Double> map = new HashMap<String, Double>();
		Cursor cursor = null;

		SQLiteDatabase db = getReadableDatabase();
		try {
			if (staticDateFormat == StaticDateFormat.ALL) {

				cursor = db.query(TABLE_NAME,
						new String[] { AccountColumn.TYPE,
								"sum(" + AccountColumn.MONEY + ")" }, null,
						null, AccountColumn.TYPE, null, null);
			} else {
				cursor = db.query(TABLE_NAME,
						new String[] { AccountColumn.TYPE, "sum(" + AccountColumn.MONEY + ")" },
						"strftime('" + staticDateFormat + "',datetime(date/1000, 'unixepoch')) = ?",
						new String[] { date }, AccountColumn.TYPE, null, null);
			}

			while (cursor.moveToNext()) {
				map.put(cursor.getString(0), cursor.getDouble(1));
			}

			// getTimeInteger(month);

		} finally {
			if (db.isOpen()) {
				db.close();
			}
		}
		return map;
	}

	public String getTimeInteger(String str) {

		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm");
		Date date;
		try {
			date = df.parse(str);
			return String.valueOf(date.getTime() / 1000);

		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return null;
	}


}
